package com.amado.easyui2.servlet;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.amado.easyui2.domain.CoachCarEntity;
import com.amado.easyui2.service.ExportCarService;
import com.amado.easyui2.service.impl.ExportCarServiceImpl;

public class ExcelExportCarServlet extends HttpServlet {
	private ExportCarService exportCarService = new ExportCarServiceImpl();
	@Override
	  //访问网址：http://localhost:8080/excelExport/servlet/ExcelExportServlet  
    public void doGet(HttpServletRequest request, HttpServletResponse response)  
            throws ServletException, IOException {  
    	
    	request.setCharacterEncoding("UTF-8");
//    	response.setCharacterEncoding("UTF-8");
//        response.setContentType("octets/stream");  
    	response.setHeader("Content-Type", "text/html;charset=utf-8");
//      response.addHeader("Content-Disposition", "attachment;filename=test.xls");  
        String excelName = "教练员信息表";  
        //转码防止乱码  
        response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");  
        String[] headers = new String[]{"id","学牌编码","创建人","修改人","原学牌号","车牌颜色","车架号","发动机号","培训车型","指标套用方式","指标编号","车身颜色","汽车品牌","汽车型号"};  
        try { 
            OutputStream out = response.getOutputStream();  
            exportExcel(excelName,headers, getList(), out);  
            out.close();  
            System.out.println("excel导出成功！");  
            
        } catch (FileNotFoundException e) {  
                e.printStackTrace();  
        } catch (IOException e) {  
                e.printStackTrace();  
        }  
    }  
	@Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)  
            throws ServletException, IOException {  
         doGet(request, response);  
    }  
    /** 
     *  
     * @Description: 模拟从数据库中查询出来的数据，一般是数据表中的几列 
     * @Auther: lujinyong 
     * @Date: 2013-8-22 下午2:53:58 
     */  
    public List<Map<String,Object>> getList(){  
    	List<CoachCarEntity> coachList = exportCarService.listAll();
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();  
        for(CoachCarEntity entity:coachList){  
            Map<String,Object> map = new HashMap<String, Object>();  
            map.put("id",entity.getId());  
            map.put("learnCardNumber", entity.getLearnCardNumber());  
            map.put("createName", entity.getCreateName());
            map.put("updateName", entity.getUpdateName()); 
            map.put("old_learn_card_number", entity.getOldLearnCardNumber());
            map.put("CARRRIAGE_NUMBER", entity.getCarrriageNumber()); 
            map.put("engine_number", entity.getEngineNumber()); 
            map.put("training_model", entity.getTrainingModel()); 
            map.put("paraphrase_way", entity.getParaphraseWay()); 
            map.put("index_number_number", entity.getIndexNumberNumber()); 
            map.put("body_color_car", entity.getBodyColorCar()); 
            map.put("automobile_brand", entity.getAutomobileBrand()); 
            map.put("car_type", entity.getCarType()); 
           
            list.add(map);  
        }  
        return list;  
    }
    /** 
     *  
     * @Description: 生成excel并导出到客户端（本地） 
     * @Auther: lujinyong 
     * @Date: 2013-8-22 下午3:05:49 
     */  
    protected void exportExcel(String title,String[] headers,List mapList,OutputStream out){  
        //声明一个工作簿  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        //生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);  
        //设置表格默认列宽度为15个字符  
        sheet.setDefaultColumnWidth(20);  
        //生成一个样式，用来设置标题样式  
        HSSFCellStyle style = workbook.createCellStyle();  
        //设置这些样式  
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        //生成一个字体  
        HSSFFont font = workbook.createFont();  
        font.setColor(HSSFColor.VIOLET.index);  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        //把字体应用到当前的样式  
        style.setFont(font);  
        // 生成并设置另一个样式,用于设置内容样式  
        HSSFCellStyle style2 = workbook.createCellStyle();  
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);  
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        // 生成另一个字体  
        HSSFFont font2 = workbook.createFont();  
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
        // 把字体应用到当前的样式  
        style2.setFont(font2);  
        //产生表格标题行  
        HSSFRow row = sheet.createRow(0);  
        for(int i = 0; i<headers.length;i++){  
            HSSFCell cell = row.createCell(i);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);  
        }  
        for (int i=0;i<mapList.size();i++) {  
            Map<String,Object> map = (Map<String, Object>) mapList.get(i);  
            row = sheet.createRow(i+1);  
            int j = 0;  
            row.createCell(j++).setCellValue(String.valueOf(map.get("id")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("learnCardNumber")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("createName")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("updateName")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("old_learn_card_number")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("car_coloc")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("CARRRIAGE_NUMBER")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("engine_number")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("training_model")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("paraphrase_way")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("index_number_number")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("body_color_car")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("automobile_brand")));  
            row.createCell(j++).setCellValue(String.valueOf(map.get("car_type")));  
           
        }  
        try {  
            workbook.write(out);  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
}
